import pandas as pd
import numpy as np
from initdatabase import *
from interactdb import *
from datetime import datetime, timedelta
import openpyxl
from common_func_defs import *
import sys


####################################################### 加工方法定义 ########################################################
'''—————————————————————————————方法：与时间相关的方法—————————————————————————————'''
def f_getbeginenddate(monthinput): #'2023-08'
    promotion_split_popul_base_month = datetime.strptime(monthinput, '%Y-%m')
    begindate2 = promotion_split_popul_base_month.replace(day=1)
    enddate2 = (promotion_split_popul_base_month.replace(day=1) + timedelta(days=32)).replace(day=1) - timedelta(days=1)
    return [begindate2.strftime('%Y-%m-%d'),enddate2.strftime('%Y-%m-%d')]

'''—————————————————————————————方法：根据预测条件查询相关历史数据—————————————————————————————'''
def get_his_data(dffront1):
    info=''
    returndict={}
    #检查dffront1：
    #predict_obj是下拉选项（选项有“大促”和“月”）不可能为空；
    #predict_obj选择大促时，仅呈现predict_year和predict_big_promotion_name和promotion_split_popul_base_month的选项。predict_year是年时间选项不可能为空、predict_big_promotion_name是下拉选项（选项源自:big_promotion_duration大促区间表的big_promotion_name的去重值）不可能为空；predict_month和overall_base_month默认为空, promotion_split_popul_base_month是月时间选项不可能为空
    #predict_obj选择月时，仅呈现predict_month和overall_base_month和promotion_split_popul_base_month的选项。predict_year和predict_big_promotion_name默认为空；predict_month和overall_base_month和promotion_split_popul_base_month是月时间选项不可能为空
    #故前端根据设定传入dffront1，不会报错，无须对dffront1添加判断

    session = get_session()
    #判断取数的起始日期
    # begindate1和enddate1是overall_base
    # begindate2和enddate2是promotion_split_popul_base
    dffront1=dffront1.to_dict(orient='records')[0]
    begindate2 = f_getbeginenddate(dffront1['promotion_split_popul_base_month'])[0]
    enddate2 = f_getbeginenddate(dffront1['promotion_split_popul_base_month'])[1]
    if dffront1['predict_obj']=='大促':
        stat_time=str(dffront1['predict_year'])+dffront1['predict_big_promotion_name']
        #取数big_promotion_duration 大促区间表（获取参考大促的起止日期信息）
        t = big_promotion_duration
        query = session.query(t.big_promotion_begin_date, t.big_promotion_end_date).filter(
            and_(t.big_promotion_year == int(dffront1['predict_year'])-1,
                 t.big_promotion_name == dffront1['predict_big_promotion_name']
                 )
        )
        df = pd.read_sql(query.statement, session.bind)
        if df.shape[0] != 1:
            raise ValueError("大促区间表中无对应参考大促的起止日期信息")
        begindate1 = df['big_promotion_begin_date'].iloc[0].strftime("%Y-%m-%d")
        enddate1 = df['big_promotion_end_date'].iloc[0].strftime("%Y-%m-%d")
    else:
        stat_time=dffront1['predict_month']
        begindate1 = f_getbeginenddate(dffront1['overall_base_month'])[0]
        enddate1 = f_getbeginenddate(dffront1['overall_base_month'])[1]

    #取历史数据需要注意的是：往往是取到历史数据后加总，若任一天数据有缺失都可能导致数据错误，所以每个取数后都加上了判断
    #判断1：取出的数据行数是否正确以判断是否有数据统计时间缺失，该情况下直接return；判断2：数据统计时间未缺失但具体的数据项缺失，该情况下默认缺失数据为空，即对数据直接加总不作处理
    #取数tmall_flagship_store_traffic_source_all_day 天猫旗舰店整体流量来源表（获取参考时间段的平台流量和广告流量GMV之和）
    t=tmall_flagship_store_traffic_source_all_day
    query = session.query(t.payment_amount).filter(
        and_(t.traffic_second_source == '汇总',
             t.traffic_third_source == '汇总',
             t.stat_time >= begindate1,
             t.stat_time <= enddate1
            )
        )
    trafficquerydf1 = pd.read_sql(query.statement, session.bind)
    if trafficquerydf1.shape[0] != ((datetime.strptime(enddate1, '%Y-%m-%d')-datetime.strptime(begindate1, '%Y-%m-%d')).days+1)*2:
        raise ValueError("历史数据缺失（计算历史平台和广告流量GMV的流量来源表历史数据缺失）")
    trafficquerydf1 = pd.DataFrame(trafficquerydf1.sum()).transpose()

    query = session.query(t.visitor_count,
                          t.payment_buyer_count,t.good_favorite_payment_buyer_count,
                          t.add_to_cart_payment_buyer_count).filter(
        and_(t.traffic_first_source == '广告流量',
             t.traffic_third_source == '汇总',
             t.stat_time >= begindate2,
             t.stat_time <= enddate2,
             or_(t.traffic_second_source == '效果广告',t.traffic_second_source == '品牌广告')
            )
        )
    trafficquerydf2 = pd.read_sql(query.statement, session.bind)
    if trafficquerydf2.shape[0] != ((datetime.strptime(enddate2, '%Y-%m-%d')-datetime.strptime(begindate2, '%Y-%m-%d')).days+1)*2:
        raise ValueError("历史数据缺失（计算历史效果和品牌广告访客数的流量来源表历史数据缺失）")
    trafficquerydf2 = pd.DataFrame(trafficquerydf2.sum()).transpose()

    #取数tmall_flagship_store_overview_day 天猫旗舰店概况日表（原日统计表）
    t=tmall_flagship_store_overview_day
    query = session.query(t.stat_time,t.gmv,t.tk_gmv,t.livestreamer_living_gmv,
                          t.promotion_gmv,t.tk_promotion_cost,
                          t.promotion_cost).filter(
        and_(t.stat_time >= begindate1,
             t.stat_time <= enddate1
            )
        )
    overviewquerydf1 = pd.read_sql(query.statement, session.bind)
    if overviewquerydf1.shape[0] != ((datetime.strptime(enddate1, '%Y-%m-%d')-datetime.strptime(begindate1, '%Y-%m-%d')).days+1):
        raise ValueError("历史数据缺失（计算gmv和费用的概况日表历史数据缺失）")

    #取数tmall_flagship_store_overview_day_input 天猫旗舰店概况日表输入表
    t=tmall_flagship_store_overview_day_input
    query = session.query(t.stat_time,t.living_slot_cost,t.fake_order_amount, t.living_commission_rate).filter(
        and_(t.stat_time >= begindate1,
             t.stat_time <= enddate1
            )
        )
    overviewquerydf2 = pd.read_sql(query.statement, session.bind)
    if overviewquerydf2.shape[0] != ((datetime.strptime(enddate1, '%Y-%m-%d')-datetime.strptime(begindate1, '%Y-%m-%d')).days+1):
        raise ValueError("历史数据缺失（计算gmv和费用的概况日表输入表历史数据缺失）")
    overviewquerydf2=pd.merge(overviewquerydf2,overviewquerydf1[['stat_time','livestreamer_living_gmv']],on='stat_time',how='left')
    overviewquerydf2['living_commission'] = overviewquerydf2.apply(lambda row: (float(row['livestreamer_living_gmv']) if pd.notna(row['livestreamer_living_gmv']) and row['livestreamer_living_gmv']!='' else 0) * (float(row['living_commission_rate']) if pd.notna(row['living_commission_rate']) and row['living_commission_rate']!='' else 0),
                       axis=1)
    overviewquerydf1= overviewquerydf1.drop(columns=['stat_time'])
    overviewquerydf2= overviewquerydf2.drop(columns=['stat_time','living_commission_rate','livestreamer_living_gmv'])
    overviewquerydf1 = pd.DataFrame(overviewquerydf1.sum()).transpose()
    overviewquerydf2 = pd.DataFrame(overviewquerydf2.sum()).transpose()

    resdf = pd.concat([trafficquerydf1,trafficquerydf2, overviewquerydf1,overviewquerydf2], axis=1)

    #tmall_flagship_store_wxt/ztc/ylmf/pxb_promotion_day 天猫旗舰店万相台/直通车/引力魔方/品销宝推广日表
    for t in [tmall_flagship_store_wxt_promotion_day,tmall_flagship_store_ztc_promotion_day,
              tmall_flagship_store_ylmf_promotion_day,tmall_flagship_store_pxb_promotion_day]:
        query = session.query(t.total_trans_amount, t.cost).filter(
            and_(t.stat_time >= begindate1,
                 t.stat_time <= enddate1
                 )
        )
        promotionchannelquerydf = pd.read_sql(query.statement, session.bind)
        if promotionchannelquerydf.shape[0] == 0:
            raise ValueError("历史数据缺失（计算历史直引万品roi的直引万品推广日表存在对应期间历史数据完全缺失的情况）")
        promotionchannelquerydf =  pd.DataFrame(promotionchannelquerydf.sum()).transpose()
        promotionchannelquerydf['his_'+t.__name__.split('_')[3]+'_roi']=promotionchannelquerydf['total_trans_amount']/promotionchannelquerydf['cost']
        resdf = pd.concat([resdf, promotionchannelquerydf[['his_'+t.__name__.split('_')[3]+'_roi']]], axis=1)
        # resdf.columns
        # ['payment_amount', 'visitor_count', 'payment_buyer_count',
        #  'good_favorite_payment_buyer_count', 'add_to_cart_payment_buyer_count',
        #  'gmv', 'tk_gmv', 'livestreamer_living_gmv', 'promotion_gmv',
        #  'tk_promotion_cost', 'promotion_cost', 'living_slot_cost',
        #  'fake_order_amount', 'living_commission', 'his_wxt_roi', 'his_ztc_roi',
        #  'his_ylmf_roi', 'his_pxb_roi']
    session.close()

    #历史数据字段
    resdf.rename(columns={'payment_amount':'his_platform_brand_traffic_gmv',
                          'visitor_count': 'his_effect_brand_adver_visitor_count',
                          'payment_buyer_count': 'his_effect_brand_adver_payment_buyer_count',
                          'good_favorite_payment_buyer_count':'his_effect_brand_adver_good_favorite_payment_buyer_count',
                          'add_to_cart_payment_buyer_count':'his_effect_brand_adver_add_to_cart_payment_buyer_count',
                          'tk_gmv':'his_tk_gmv','livestreamer_living_gmv':'his_living_gmv',
                          'promotion_gmv':'his_promotion_gmv','tk_promotion_cost':'his_tk_fee',
                          'promotion_cost':'his_promotion_fee'
    }, inplace=True)
    # resdf.columns
    # ['his_platform_brand_traffic_gmv',
    #  'his_effect_brand_adver_visitor_count',
    #  'his_effect_brand_adver_payment_buyer_count',
    #  'his_effect_brand_adver_good_favorite_payment_buyer_count',
    #  'his_effect_brand_adver_add_to_cart_payment_buyer_count', 'gmv',
    #  'his_tk_gmv', 'his_living_gmv', 'his_promotion_gmv', 'his_tk_fee',
    #  'his_promotion_fee', 'living_slot_cost', 'fake_order_amount',
    #  'living_commission', 'his_wxt_roi', 'his_ztc_roi', 'his_ylmf_roi',
    #  'his_pxb_roi']

    #因为resdf的一行数据都是sum()得到的，所以是数值应该没有问题；关于是否需要判断除数不为0：是数据行数没有问题然后汇总，应该不会所有的数据都缺失吧，所以不加判断
    resdf['his_effect_brand_adver_visit_buy_rate']=resdf['his_effect_brand_adver_payment_buyer_count']/resdf['his_effect_brand_adver_visitor_count']
    resdf['his_effect_brand_adver_collect_buy_rate']=resdf['his_effect_brand_adver_good_favorite_payment_buyer_count']/resdf['his_effect_brand_adver_payment_buyer_count']
    resdf['his_effect_brand_adver_add_buy_rate']=resdf['his_effect_brand_adver_add_to_cart_payment_buyer_count']/resdf['his_effect_brand_adver_payment_buyer_count']
    resdf['his_delete_fake_gmv']=resdf['gmv']-resdf['fake_order_amount']
    resdf['his_gmv_overlap_degree']=resdf['his_platform_brand_traffic_gmv']/resdf['his_delete_fake_gmv']
    resdf['his_delete_fake_gmv_overlap']=resdf['his_delete_fake_gmv']*resdf['his_gmv_overlap_degree']
    resdf['his_free_gmv']=resdf['his_delete_fake_gmv_overlap']-resdf['his_tk_gmv']-resdf['his_living_gmv']-resdf['his_promotion_gmv']
    resdf['his_living_fee']=resdf['living_commission']+resdf['living_slot_cost']
    resdf['his_tk_gmv_ratio_g1']=resdf['his_tk_gmv']/resdf['his_delete_fake_gmv']
    resdf['his_living_gmv_ratio_g1']=resdf['his_living_gmv']/resdf['his_delete_fake_gmv']
    resdf['his_promotion_gmv_ratio_g1']=resdf['his_promotion_gmv']/resdf['his_delete_fake_gmv']
    resdf['his_free_gmv_ratio_g1']=resdf['his_free_gmv']/resdf['his_delete_fake_gmv']
    # resdf['his_tk_gmv_ratio_r1']=resdf['his_tk_gmv_ratio_g1']
    # resdf['his_living_gmv_ratio_r1']=resdf['his_living_gmv_ratio_g1']
    resdf['his_promotion_of_promotion_free_ratio_g2']=resdf['his_promotion_gmv_ratio_g1']/(resdf['his_promotion_gmv_ratio_g1']+resdf['his_free_gmv_ratio_g1'])
    resdf['his_free_of_promotion_free_ratio_g2']=resdf['his_free_gmv_ratio_g1']/(resdf['his_promotion_gmv_ratio_g1']+resdf['his_free_gmv_ratio_g1'])
    resdf['his_tk_roi']=resdf['his_tk_gmv']/resdf['his_tk_fee']
    resdf['his_living_roi']=resdf['his_living_gmv']/resdf['his_living_fee']
    resdf['stat_time']=[stat_time]

    returnfrontdf=resdf[[
                         'his_gmv_overlap_degree',
                         'his_tk_gmv_ratio_g1','his_living_gmv_ratio_g1','his_promotion_gmv_ratio_g1','his_free_gmv_ratio_g1',
                         # 'his_tk_gmv_ratio_r1','his_living_gmv_ratio_r1',
                         'his_promotion_of_promotion_free_ratio_g2','his_free_of_promotion_free_ratio_g2',
                         'his_tk_roi','his_living_roi','his_ztc_roi','his_ylmf_roi','his_wxt_roi','his_pxb_roi',
                         'his_effect_brand_adver_visit_buy_rate','his_effect_brand_adver_collect_buy_rate','his_effect_brand_adver_add_buy_rate']]
    usedf=resdf[['stat_time','his_delete_fake_gmv','his_delete_fake_gmv_overlap','his_gmv_overlap_degree',
        'his_tk_gmv','his_living_gmv','his_promotion_gmv','his_free_gmv',
        'his_tk_gmv_ratio_g1','his_living_gmv_ratio_g1','his_promotion_gmv_ratio_g1','his_free_gmv_ratio_g1']]
    returndict['returnfrontdf']=returnfrontdf
    returndict['usedf']=usedf
    return [info,returndict]

'''—————————————————————————————方法：根据查询得到的历史数据和用户指定指标进行预测—————————————————————————————'''
def check_process_dffront2(dffront2,usedf):
    info=''
    returndict={}
    #检查dffront2：确保均为可计算的数值字段
    stat_time=usedf['stat_time'].iloc[0]
    dffront2['stat_time']=stat_time
    for col in [x for x in dffront2.columns.to_list() if x != 'stat_time']:
        try:
            dffront2[col] = dffront2[col].astype(float)
        except:
            if not ((col=='tk_given_gmv' and  dffront2[col].iloc[0]=='') or (col=='living_given_gmv' and  dffront2[col].iloc[0]=='')):
                raise ValueError("前端传入数据中存在非可计算数值的字段")

    whethertwokinds=True
    if dffront2[['predict_tk_gmv_ratio_g1', 'predict_living_gmv_ratio_g1', 'predict_promotion_gmv_ratio_g1','predict_free_gmv_ratio_g1']].sum(axis=1).iloc[0] != dffront2['predict_gmv_overlap_degree'].iloc[0]:
        raise ValueError("预测淘客、直播、推广、免费渠道GMV占比(用于目标表1）与预测GMV渠道重合度数值关系错误")
    if dffront2[['predict_promotion_of_promotion_free_ratio_g2', 'predict_free_of_promotion_free_ratio_g2']].sum(axis=1).iloc[0] !=1:
        raise ValueError("预测推广和免费渠道GMV相对占比(用于目标表2）数值关系错误")
    if dffront2['tk_given_gmv'].iloc[0]=='' or  dffront2['living_given_gmv'].iloc[0]=='':
        whethertwokinds=False
    print(whethertwokinds)
    dffront2['calculate_promotion_given_fee']=dffront2[['ztc_given_fee', 'ylmf_given_fee', 'wxt_given_fee','pxb_given_fee']].sum(axis=1).iloc[0]
    dffront2['calculate_ztc_gmv']=dffront2['ztc_given_fee']*dffront2['predict_ztc_roi']
    dffront2['calculate_ylmf_gmv']=dffront2['ylmf_given_fee']*dffront2['predict_ylmf_roi']
    dffront2['calculate_wxt_gmv']=dffront2['wxt_given_fee']*dffront2['predict_wxt_roi']
    dffront2['calculate_pxb_gmv']=dffront2['pxb_given_fee']*dffront2['predict_pxb_roi']
    dffront2['calculate_promotion_gmv']=dffront2[['calculate_ztc_gmv','calculate_ylmf_gmv','calculate_wxt_gmv','calculate_pxb_gmv']].sum(axis=1).iloc[0]
    dffront2['calculate_promotion_roi']=dffront2['calculate_promotion_gmv']/dffront2['calculate_promotion_given_fee']
    dffront2['calculate_promotion_gmv_ratio_r1']=dffront2['calculate_promotion_gmv']/dffront2['total_gmv_r1']

    dffront2['predict_tk_gmv_ratio_r1']=dffront2['predict_tk_gmv_ratio_g1']
    dffront2['predict_living_gmv_ratio_r1']=dffront2['predict_living_gmv_ratio_g1']

    if dffront2[['predict_tk_gmv_ratio_r1','predict_living_gmv_ratio_r1','calculate_promotion_gmv_ratio_r1']].sum(axis=1).iloc[0]  > dffront2['predict_gmv_overlap_degree'].iloc[0]:
        raise ValueError("预测淘客、直播渠道GMV占比(结果表1），计算推广渠道GMV占比（结果表1）与预测GMV渠道重合度数值关系错误")

    #计算得到目标表1
    g1=dffront2[['stat_time',
                 'total_target_gmv','predict_gmv_overlap_degree',
                 'predict_tk_gmv_ratio_g1', 'predict_living_gmv_ratio_g1', 'predict_promotion_gmv_ratio_g1','predict_free_gmv_ratio_g1',
                 'predict_tk_roi', 'predict_living_roi','calculate_promotion_roi'
                 ]]
    g1['calculate_total_target_gmv_overlap']=g1['total_target_gmv']*g1['predict_gmv_overlap_degree']
    g1['calculate_tk_target_gmv_g1']=g1['total_target_gmv']*g1['predict_tk_gmv_ratio_g1']
    g1['calculate_living_target_gmv_g1']=g1['total_target_gmv']*g1['predict_living_gmv_ratio_g1']
    g1['calculate_promotion_target_gmv_g1']=g1['total_target_gmv']*g1['predict_promotion_gmv_ratio_g1']
    g1['calculate_free_target_gmv_g1']=g1['total_target_gmv']*g1['predict_free_gmv_ratio_g1']
    g1['calculate_tk_fee_g1']=g1['calculate_tk_target_gmv_g1']/g1['predict_tk_roi']
    g1['calculate_living_fee_g1']=g1['calculate_living_target_gmv_g1']/g1['predict_living_roi']
    g1['calculate_promotion_fee_g1']=g1['calculate_promotion_target_gmv_g1']/g1['calculate_promotion_roi']
    g1['calculate_total_fee_g1']=g1[['calculate_tk_fee_g1','calculate_living_fee_g1','calculate_promotion_fee_g1']].sum(axis=1).iloc[0]
    new_order = ['stat_time',
                 'total_target_gmv', 'calculate_total_target_gmv_overlap',
                 'calculate_tk_fee_g1','calculate_living_fee_g1','calculate_promotion_fee_g1','calculate_total_fee_g1',
                 'calculate_tk_target_gmv_g1', 'calculate_living_target_gmv_g1', 'calculate_promotion_target_gmv_g1', 'calculate_free_target_gmv_g1','calculate_total_target_gmv_overlap',
                 'predict_tk_gmv_ratio_g1', 'predict_living_gmv_ratio_g1', 'predict_promotion_gmv_ratio_g1', 'predict_free_gmv_ratio_g1','predict_gmv_overlap_degree',
                 'predict_tk_roi', 'predict_living_roi', 'calculate_promotion_roi'
                 ]
    g1 = g1[new_order]

    if whethertwokinds==True:
        dffront2['calculate_promotion_gmv_ratio_g2'] = (dffront2['predict_gmv_overlap_degree'] - (
                (dffront2['tk_given_gmv'] + dffront2['living_given_gmv']) / dffront2['total_target_gmv'])) * dffront2[
                                                           'predict_promotion_of_promotion_free_ratio_g2']
        dffront2['calculate_free_gmv_ratio_g2'] = (dffront2['predict_gmv_overlap_degree'] - (
                (dffront2['tk_given_gmv'] + dffront2['living_given_gmv']) / dffront2['total_target_gmv'])) * dffront2[
                                                      'predict_free_of_promotion_free_ratio_g2']
        #计算得到目标表2
        g2=dffront2[['stat_time',
                     'total_target_gmv','predict_gmv_overlap_degree',
                     'tk_given_gmv','living_given_gmv',
                     'calculate_promotion_gmv_ratio_g2','calculate_free_gmv_ratio_g2',
                     'predict_tk_roi', 'predict_living_roi','calculate_promotion_roi'
                     ]]
        g2['calculate_total_target_gmv_overlap']=g2['total_target_gmv']*g2['predict_gmv_overlap_degree']
        g2['calculate_tk_gmv_ratio_g2']=g2['tk_given_gmv']/g1['total_target_gmv']
        g2['calculate_living_gmv_ratio_g2']=g2['living_given_gmv']/g1['total_target_gmv']
        g2['calculate_promotion_target_gmv_g2']=g2['total_target_gmv']*g2['calculate_promotion_gmv_ratio_g2']
        g2['calculate_free_target_gmv_g2']=g2['total_target_gmv']*g2['calculate_free_gmv_ratio_g2']
        g2['calculate_tk_fee_g2']=g2['tk_given_gmv']/g2['predict_tk_roi']
        g2['calculate_living_fee_g2']=g2['living_given_gmv']/g2['predict_living_roi']
        g2['calculate_promotion_fee_g2']=g2['calculate_promotion_target_gmv_g2']/g2['calculate_promotion_roi']
        g2['calculate_total_fee_g2']=g2[['calculate_tk_fee_g2','calculate_living_fee_g2','calculate_promotion_fee_g2']].sum(axis=1).iloc[0]
        new_order = ['stat_time',
                     'total_target_gmv', 'calculate_total_target_gmv_overlap',
                     'calculate_tk_fee_g2','calculate_living_fee_g2','calculate_promotion_fee_g2','calculate_total_fee_g2',
                     'tk_given_gmv', 'living_given_gmv', 'calculate_promotion_target_gmv_g2', 'calculate_free_target_gmv_g2','calculate_total_target_gmv_overlap',
                     'calculate_tk_gmv_ratio_g2', 'calculate_living_gmv_ratio_g2', 'calculate_promotion_gmv_ratio_g2', 'calculate_free_gmv_ratio_g2','predict_gmv_overlap_degree',
                     'predict_tk_roi', 'predict_living_roi', 'calculate_promotion_roi'
                     ]
        g2 = g2[new_order]

    #计算得到结果表1
    r1=dffront2[[#'stat_time',
                 'total_gmv_r1','predict_gmv_overlap_degree',
                 'calculate_promotion_gmv',
                 'predict_tk_gmv_ratio_r1','predict_living_gmv_ratio_r1',
                 'predict_tk_roi', 'predict_living_roi', 'calculate_promotion_roi',
                 'calculate_promotion_given_fee'
                 ]]
    r1 =  pd.concat([r1, usedf], axis=1)
    r1['calculate_total_gmv_overlap_r1']=r1['total_gmv_r1']*r1['predict_gmv_overlap_degree']
    r1['calculate_promotion_gmv_ratio_r1']=r1['calculate_promotion_gmv']/r1['total_gmv_r1']
    r1['calculate_free_gmv_ratio_r1']=r1['predict_gmv_overlap_degree']-(r1['predict_tk_gmv_ratio_r1']+r1['predict_living_gmv_ratio_r1']+r1['calculate_promotion_gmv_ratio_r1'])
    r1['calculate_tk_gmv_r1']=r1['total_gmv_r1']*r1['predict_tk_gmv_ratio_r1']
    r1['calculate_living_gmv_r1']=r1['total_gmv_r1']*r1['predict_living_gmv_ratio_r1']
    r1['calculate_free_gmv']=r1['total_gmv_r1']*r1['calculate_free_gmv_ratio_r1']
    r1['calculate_tk_fee_r1']=r1['calculate_tk_gmv_r1']/r1['predict_tk_roi']
    r1['calculate_living_fee_r1']=r1['calculate_living_gmv_r1']/r1['predict_living_roi']
    r1['calculate_total_fee_r1']=r1[['calculate_tk_fee_r1','calculate_living_fee_r1','calculate_promotion_given_fee']].sum(axis=1).iloc[0]
    # r1['calculate_total_gmv_incre_r1']=r1['total_gmv_r1']-r1['his_delete_fake_gmv']
    # r1['calculate_tk_gmv_incre_r1']=r1['calculate_tk_gmv_r1']-r1['his_tk_gmv']
    # r1['calculate_living_gmv_incre_r1']=r1['calculate_living_gmv_r1']-r1['his_living_gmv']
    # r1['calculate_promotion_gmv_incre_r1']=r1['calculate_promotion_gmv']-r1['his_promotion_gmv']
    # r1['calculate_free_gmv_incre_r1']=r1['calculate_free_gmv']-r1['his_free_gmv']
    new_order = ['stat_time',
                 'total_gmv_r1', 'calculate_total_gmv_overlap_r1',
                 'calculate_tk_fee_r1','calculate_living_fee_r1','calculate_promotion_given_fee','calculate_total_fee_r1',
                 'calculate_tk_gmv_r1', 'calculate_living_gmv_r1', 'calculate_promotion_gmv', 'calculate_free_gmv','calculate_total_gmv_overlap_r1',
                 'predict_tk_gmv_ratio_r1', 'predict_living_gmv_ratio_r1', 'calculate_promotion_gmv_ratio_r1', 'calculate_free_gmv_ratio_r1','predict_gmv_overlap_degree',
                 'predict_tk_roi', 'predict_living_roi', 'calculate_promotion_roi',

                 'his_delete_fake_gmv', 'his_delete_fake_gmv_overlap',
                 'his_tk_gmv','his_living_gmv', 'his_promotion_gmv', 'his_free_gmv','his_delete_fake_gmv_overlap',
                 'his_tk_gmv_ratio_g1', 'his_living_gmv_ratio_g1','his_promotion_gmv_ratio_g1', 'his_free_gmv_ratio_g1','his_gmv_overlap_degree'
                 ]
    r1 = r1[new_order]

    if whethertwokinds==True:
        #计算得到结果表2
        r2=dffront2[[#'stat_time',
                     'predict_gmv_overlap_degree',
                     'calculate_promotion_gmv',
                     'tk_given_gmv', 'living_given_gmv',
                     'predict_tk_gmv_ratio_r1','predict_living_gmv_ratio_r1',
                     'predict_tk_roi', 'predict_living_roi', 'calculate_promotion_roi',
                     'calculate_promotion_given_fee'
                     ]]
        r2 = pd.concat([r2, usedf, r1[['calculate_free_gmv']]], axis=1)
        r2['calculate_total_gmv_overlap_r2'] = r2[['tk_given_gmv', 'living_given_gmv',
                                                   'calculate_promotion_gmv', 'calculate_free_gmv']].sum(axis=1).iloc[0]
        r2['calculate_total_gmv_r2'] = r2['calculate_total_gmv_overlap_r2'] / r2['predict_gmv_overlap_degree']
        r2['calculate_tk_gmv_ratio_r2'] = r2['tk_given_gmv'] / r2['calculate_total_gmv_r2']
        r2['calculate_living_gmv_ratio_r2'] = r2['living_given_gmv'] / r2['calculate_total_gmv_r2']
        r2['calculate_promotion_gmv_ratio_r2'] = r2['calculate_promotion_gmv'] / r2['calculate_total_gmv_r2']
        r2['calculate_free_gmv_ratio_r2'] = r2['calculate_free_gmv'] / r2['calculate_total_gmv_r2']
        r2['calculate_tk_fee_r2']=r2['tk_given_gmv']/r2['predict_tk_roi']
        r2['calculate_living_fee_r2']=r2['living_given_gmv']/r2['predict_living_roi']
        r2['calculate_total_fee_r2']=r2[['calculate_tk_fee_r2','calculate_living_fee_r2','calculate_promotion_given_fee']].sum(axis=1).iloc[0]
        # r2['calculate_total_gmv_incre_r2']=r2['calculate_total_gmv_r2']-r2['his_delete_fake_gmv']
        # r2['calculate_tk_gmv_incre_r2']=r2['tk_given_gmv']-r2['his_tk_gmv']
        # r2['calculate_living_gmv_incre_r2']=r2['living_given_gmv']-r2['his_living_gmv']
        # r2['calculate_promotion_gmv_incre_r2']=r2['calculate_promotion_gmv']-r2['his_promotion_gmv']
        # r2['calculate_free_gmv_incre_r2']=r2['calculate_free_gmv']-r2['his_free_gmv']
        new_order = ['stat_time',
                     'calculate_total_gmv_r2', 'calculate_total_gmv_overlap_r2',
                     'calculate_tk_fee_r2','calculate_living_fee_r2','calculate_promotion_given_fee','calculate_total_fee_r2',
                     'tk_given_gmv', 'living_given_gmv', 'calculate_promotion_gmv', 'calculate_free_gmv','calculate_total_gmv_overlap_r2',
                     'calculate_tk_gmv_ratio_r2', 'calculate_living_gmv_ratio_r2', 'calculate_promotion_gmv_ratio_r2', 'calculate_free_gmv_ratio_r2','predict_gmv_overlap_degree',
                     'predict_tk_roi', 'predict_living_roi', 'calculate_promotion_roi',

                     'his_delete_fake_gmv', 'his_delete_fake_gmv_overlap',
                     'his_tk_gmv','his_living_gmv', 'his_promotion_gmv', 'his_free_gmv','his_delete_fake_gmv_overlap',
                     'his_tk_gmv_ratio_g1', 'his_living_gmv_ratio_g1','his_promotion_gmv_ratio_g1', 'his_free_gmv_ratio_g1','his_gmv_overlap_degree'
                     ]
        r2 = r2[new_order]

   #三种结果的说明
    explain1 = '渠道预测时间：'+stat_time+';说明：目标表按历史各渠道GMV占比分配GMV，根据历史ROI计算相应费用；结果表根据现有费用预估可完成的总GMV和推广GMV，根据历史占比估算淘客和直播GMV，并计算免费GMV，根据历史ROI计算相应费用；缺口表是目标表与结果表相减，缺口由各渠道补齐'
    explain2 = '渠道预测时间：'+stat_time+';说明：目标表按历史各渠道GMV占比分配GMV，根据历史ROI计算相应费用；结果表根据品牌方给定的淘客和直播能达成的GMV，现有费用预估可完成的推广GMV，和通过自然计算得到的免费GMV，汇总总GMV，根据历史ROI计算相应费用；缺口表是目标表与结果表相减，缺口由各渠道补齐'
    explain3 = '渠道预测时间：'+stat_time+';说明：目标表按品牌方给定的淘客和直播能达成的GMV，历史推广和免费渠道的相对GMV占比分配GMV，根据历史ROI计算相应费用；结果表根据品牌方给定的淘客和直播能达成的GMV，现有费用预估可完成的推广GMV，和通过自然计算得到的免费GMV，汇总总GMV，根据历史ROI计算相应费用；缺口表是目标表与结果表相减，缺口仅由推广和免费渠道补齐'

    #计算得到缺口表11、12、22
    g1comput=g1[['total_target_gmv','calculate_total_target_gmv_overlap',#(calculate_total_target_gmv_overlap在g1中有两列)
    'calculate_tk_target_gmv_g1','calculate_living_target_gmv_g1','calculate_promotion_target_gmv_g1','calculate_free_target_gmv_g1',
    'calculate_tk_fee_g1','calculate_living_fee_g1','calculate_promotion_fee_g1']]
    g1comput=g1comput.loc[:, ~g1comput.columns.duplicated()]
    r1comput=r1[['total_gmv_r1','calculate_total_gmv_overlap_r1',#(calculate_total_gmv_overlap_r1在r1中有两列)
    'calculate_tk_gmv_r1','calculate_living_gmv_r1','calculate_promotion_gmv','calculate_free_gmv',
    'calculate_tk_fee_r1','calculate_living_fee_r1','calculate_promotion_given_fee'
    ]]
    r1comput=r1comput.loc[:, ~r1comput.columns.duplicated()]
    if whethertwokinds==True:
        g2comput=g2[['total_target_gmv','calculate_total_target_gmv_overlap',#(calculate_total_target_gmv_overlap在g2中有两列)
        'tk_given_gmv','living_given_gmv','calculate_promotion_target_gmv_g2','calculate_free_target_gmv_g2',
        'calculate_tk_fee_g2','calculate_living_fee_g2','calculate_promotion_fee_g2']]
        g2comput = g2comput.loc[:, ~g2comput.columns.duplicated()]
        r2comput=r2[['calculate_total_gmv_r2','calculate_total_gmv_overlap_r2',#(calculate_total_gmv_overlap_r2在r2中有两列)
        'tk_given_gmv','living_given_gmv','calculate_promotion_gmv','calculate_free_gmv',
        'calculate_tk_fee_r2','calculate_living_fee_r2','calculate_promotion_given_fee']]
        r2comput = r2comput.loc[:, ~r2comput.columns.duplicated()]
    gaplist=[]
    if whethertwokinds==True:
        templ = [[g1,r1,g1comput,r1comput],[g1,r2,g1comput,r2comput],[g2,r2,g2comput,r2comput]]
    else:
        templ = [[g1,r1,g1comput,r1comput]]
    for dfpair in templ:
        g=dfpair[0]
        r=dfpair[1]
        gcomput=dfpair[2]
        rcomput=dfpair[3]
        gap = np.array(gcomput) - np.array(rcomput)
        gap = pd.DataFrame(gap, columns=['calculate_total_gmv_gap','calculate_total_gmv_overlap_gap',
        'calculate_tk_gmv_gap','calculate_living_gmv_gap','calculate_promotion_gmv_gap','calculate_free_gmv_gap',
        'calculate_tk_fee_gap','calculate_living_fee_gap','calculate_promotion_fee_gap'
        ])
        gap= pd.concat([gap,  r[['stat_time','predict_tk_roi', 'predict_living_roi', 'calculate_promotion_roi','predict_gmv_overlap_degree']]], axis=1)
        gap['calculate_tk_gmv_gap_ratio']=gap['calculate_tk_gmv_gap']/gap['calculate_total_gmv_gap']
        gap['calculate_living_gmv_gap_ratio']=gap['calculate_living_gmv_gap']/gap['calculate_total_gmv_gap']
        gap['calculate_promotion_gmv_gap_ratio']=gap['calculate_promotion_gmv_gap']/gap['calculate_total_gmv_gap']
        gap['calculate_free_gmv_gap_ratio']=gap['calculate_free_gmv_gap']/gap['calculate_total_gmv_gap']
        gap['calculate_total_fee_gap']=gap[['calculate_tk_fee_gap','calculate_living_fee_gap','calculate_promotion_fee_gap']].sum(axis=1).iloc[0]
        gap = pd.concat([gap,
                         dffront2[['avg_trans_value','predict_effect_brand_adver_visit_buy_rate','predict_effect_brand_adver_collect_buy_rate','predict_effect_brand_adver_add_buy_rate']]], axis=1)
        gap['calculate_missing_buyers_count']=gap['calculate_promotion_gmv_gap']/gap['avg_trans_value']
        gap['calculate_missing_visitors_count']=gap['calculate_missing_buyers_count']/gap['predict_effect_brand_adver_visit_buy_rate']
        gap['calculate_missing_collect_buyers_count']=gap['calculate_missing_buyers_count']*gap['predict_effect_brand_adver_collect_buy_rate']
        gap['calculate_missing_add_buyers_count']=gap['calculate_missing_buyers_count']*gap['predict_effect_brand_adver_add_buy_rate']
        new_order = ['stat_time',
                     'calculate_total_gmv_gap', 'calculate_total_gmv_overlap_gap',
                     'calculate_tk_fee_gap','calculate_living_fee_gap','calculate_promotion_fee_gap','calculate_total_fee_gap',
                     'calculate_tk_gmv_gap', 'calculate_living_gmv_gap', 'calculate_promotion_gmv_gap', 'calculate_free_gmv_gap','calculate_total_gmv_overlap_gap',
                     'calculate_tk_gmv_gap_ratio', 'calculate_living_gmv_gap_ratio', 'calculate_promotion_gmv_gap_ratio', 'calculate_free_gmv_gap_ratio','predict_gmv_overlap_degree',
                     'predict_tk_roi', 'predict_living_roi', 'calculate_promotion_roi',
                     'calculate_missing_buyers_count','calculate_missing_visitors_count','calculate_missing_collect_buyers_count','calculate_missing_add_buyers_count'
                    ]
        gap = gap[new_order]
        gaplist.append(gap)
    gap11 = gaplist[0]
    if whethertwokinds==True:
        gap12 = gaplist[1]
        gap22 = gaplist[2]
        returndict['predict_resdfs']=[[g1,r1,gap11,explain1],[g1,r2,gap12,explain2],[g2,r2,gap22,explain3]]
        return [info, returndict]
    else:
        returndict['predict_resdfs']=[[g1, r1, gap11,explain1]]
        return [info, returndict]

'''—————————————————————————————方法：定位结果表Excel模块中特定位置—————————————————————————————'''
def find_value_in_range(sheet, target_value, start_row, end_row, start_column, end_column):
    for row in range(start_row, end_row + 1):
        for column in range(start_column, end_column + 1):
            cell_value = sheet.cell(row=row, column=column).value
            if cell_value == target_value:
                return row, column
    return None



'''—————————————————————————————方法：展示预测结果—————————————————————————————'''
def display_predict_res(templateloc,predict_resdfs):
    workbook = openpyxl.load_workbook(templateloc) # 打开Excel模板
    for i in range(0,len(predict_resdfs)+1-1):
        worksheet = workbook['predictres'+str(i+1)]  # 选择工作表
        predict_resdf=predict_resdfs[i]
        g=predict_resdf[0]
        r=predict_resdf[1]
        gap=predict_resdf[2]
        explain=predict_resdf[3]
        worksheet.cell(row=1, column=1, value=explain)
        for m in range(1,len(g.columns)):
            row, column = find_value_in_range(worksheet, target_value='v' + str(m),
                                              start_row=3, end_row=10, start_column=2,end_column=5)
            worksheet.cell(row=row, column=column, value=g.iloc[0, m])
        for m in range(1,len(r.columns)):
            row, column = find_value_in_range(worksheet, target_value='v' + str(m),
                                              start_row=3, end_row=10, start_column=6,end_column=11)
            worksheet.cell(row=row, column=column, value=r.iloc[0, m])
        for m in range(1,len(gap.columns)):
            row, column = find_value_in_range(worksheet, target_value='v' + str(m),
                                              start_row=3, end_row=10, start_column=12,end_column=17)
            worksheet.cell(row=row, column=column, value=gap.iloc[0, m])
    workbook.save('channel_predict_res.xlsx')


####################################################### 前端调用加工方法（请仔细阅读说明） ########################################################
'''—————————————————————————————功能：其他预测-渠道预测—————————————————————————————'''
###因为涉及一次交互，所以前端需要分两步
###前端第一步
##①根据用户选择预测大促还是预测月，得到dffront1
#预测大促
dffront1 = pd.DataFrame({'predict_obj': ['大促'], 'predict_year': [2023], 'predict_big_promotion_name': ['双十一'],
                         'predict_month': [np.nan],'overall_base_month': [np.nan],'promotion_split_popul_base_month': ['2023-08']
                         })
# #预测月
# dffront1 = pd.DataFrame({'predict_obj': ['月'], 'predict_year': [np.nan], 'predict_big_promotion_name': [np.nan],
#                          'predict_month': ['2023-12'],'overall_base_month': ['2022-12'],'promotion_split_popul_base_month': ['2023-11']
#                         })

##②调用get_his_data(dffront1)方法
res=get_his_data(dffront1)
if res[0]=='': #info为空，说明过程正确
    returnfrontdf=res[1]['returnfrontdf']
    usedf=res[1]['usedf']
    #进入前端第二步
else:
    print(res[0])#将info内容返回给用户
    sys.exit() #程序终止


###前端第二步
##①将前一步得到的returnfrontdf呈现给用户，即dffront2中第4排到第9排(returnfrontdf均为his_开头全改为了predict_)的内容，让用户可以修改值，以及新的输入项，即dffront2中第1排到第3排中的内容，需要注意的是tk_given_gmv和living_given_gmv无输入值时也有意义，无输入值时请传''值，得到dffront2
##也就是将上一步得到的returnfrontdf的用户修改值和用户新输入的内容整合成dffront2(之所以分两步，用户需要根据前一步得到的结果来辅助输入)
dffront2 = pd.DataFrame({# 以下是公司输入本次预测的GMV目标，客单价和本次预测的人群推广预测处得到的四种渠道特定推广费用下能达成的GMV
                         'total_target_gmv': [32000000], 'avg_trans_value': [74], 'total_gmv_r1': [27404670],
                         # 以下是公司输入认为淘客和直播能达成的GMV
                         'tk_given_gmv': [6500000], 'living_given_gmv': [4000000],
                         # 'tk_given_gmv': [''], 'living_given_gmv': [''],
                         # 以下是根据四种推广渠道的费用和历史roi估计推广能达成的GMV
                         'ztc_given_fee': [770000], 'ylmf_given_fee': [190000], 'wxt_given_fee': [800000], 'pxb_given_fee': [420000],
                         # 以下都是让用户对由历史数据得到的参考数据进行调整
                         'predict_tk_roi': [1.9], 'predict_living_roi': [3.0],
                         'predict_ztc_roi': [1.9], 'predict_ylmf_roi': [0.9], 'predict_wxt_roi': [2.7],'predict_pxb_roi': [3.0],
                         'predict_effect_brand_adver_visit_buy_rate': [0.12],'predict_effect_brand_adver_collect_buy_rate': [0.07],'predict_effect_brand_adver_add_buy_rate': [0.45],
                         'predict_gmv_overlap_degree': [1.04],
                         'predict_tk_gmv_ratio_g1': [0.24],'predict_living_gmv_ratio_g1': [0.17], 'predict_promotion_gmv_ratio_g1': [0.18],'predict_free_gmv_ratio_g1': [0.45],
                         'predict_promotion_of_promotion_free_ratio_g2': [0.29],'predict_free_of_promotion_free_ratio_g2': [0.71]})
#注：此处的dffront2是测试输入，真实场景下是用户输入内容
usedf= pd.DataFrame({'stat_time': ['2023双十一'],'his_delete_fake_gmv': [25661713],'his_delete_fake_gmv_overlap': [26058163],'his_gmv_overlap_degree': [1.02],'his_tk_gmv': [4626313],'his_living_gmv': [4290000],
'his_promotion_gmv': [4297634],'his_free_gmv': [14210096],'his_tk_gmv_ratio_g1': [0.18],'his_living_gmv_ratio_g1': [0.16],'his_promotion_gmv_ratio_g1': [0.16],'his_free_gmv_ratio_g1':[0.55]
                        })
#注：此处的usedf是测试输入，真实场景下是第一步方法得到的usedf

##②调用check_process_dffront2(dffront2,usedf)方法，传入dffront2和前一步得到的usedf
res=check_process_dffront2(dffront2, usedf)
if res[0]=='': #info为空，说明过程正确
    predict_resdfs=res[1]['predict_resdfs']
    templateloc = 'channel_predict_template.xlsx'  # 预测结果存储路径："channel_predict_res.xlsx"
    display_predict_res(templateloc, predict_resdfs)
else:
    print(res[0])#将info内容返回给用户
    sys.exit() #程序终止





